/*
 *接口编写处...
*如果接口需要做Action的权限验证，请在Action上使用属性
*如: [ApiActionPermission("Fk_yusuan",Enums.ActionPermissionOptions.Search)]
 */
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.AspNetCore.Http;
using VOL.Entity.DomainModels;
using VOL.MyTable.IServices;
using Microsoft.AspNetCore.Authorization;
using System.Collections;
using VOL.Core.DBManager;
using System.Linq;
using VOL.MyTable.Repositories;
using Newtonsoft.Json;
using VOL.MyTable.IRepositories;
using System.Globalization;
using Microsoft.EntityFrameworkCore;
using Newtonsoft.Json.Linq;
using VOL.WebApi.Controllers.MyTable;
using VOL.Core.Utilities;
using sysio = System.IO;
using sysnet = System.Net;
using prov = System.Globalization;
using VOL.Core.CacheManager;
using VOL.Core.Extensions.AutofacManager;
using VOL.Core.ManageUser;
using System.Reflection;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using VOL.Core.Extensions;
using System.IO;

namespace VOL.MyTable.Controllers
{
    public partial class Fk_yusuanController
    {
        private readonly IFk_yusuanService _service;//访问业务代码
        private readonly IHttpContextAccessor _httpContextAccessor;



        [ActivatorUtilitiesConstructor]
        public Fk_yusuanController(
            IFk_yusuanService service,
            IHttpContextAccessor httpContextAccessor
        )
        : base(service)
        {
            _service = service;
            _httpContextAccessor = httpContextAccessor;
        }

        /// <summary>
        /// 获取HOME页基础数据
        /// </summary>
        /// <returns></returns>
        [HttpPost, Route("get_yusuan_home"), AllowAnonymous]
        public object get_yusuan_home(int ls_typeid)
        {
            Hashtable rtn_hst = new Hashtable();
            string sql = "";
            if (ls_typeid == 1)
            {
                //sql = "select sum(tsl01) s_tsl01,sum(tsl02) s_tsl02 from fk_yusuan where convert(varchar(4),getdate(),120)=convert(varchar(4),gjahr,120)";
                //dynamic[] sum_yuan = DBServerProvider.SqlDapper.QueryList<object>(sql, null).ToArray();
                //rtn_hst.Add("sum_yuan", sum_yuan);
                IQueryable<Fk_yusuan> Fk_yusuan_queryeable = Fk_yusuanRepository.
              Instance.FindAsIQueryable(t => (t.gjahr == DateTime.Now.Year));
                decimal?[] sum_yu = new decimal?[12];
                sum_yu[0] = Fk_yusuan_queryeable.Sum(t => (t.tsl01));
                sum_yu[1] = Fk_yusuan_queryeable.Sum(t => (t.tsl02));
                sum_yu[2] = Fk_yusuan_queryeable.Sum(t => (t.tsl03));
                sum_yu[3] = Fk_yusuan_queryeable.Sum(t => (t.tsl04));
                sum_yu[4] = Fk_yusuan_queryeable.Sum(t => (t.tsl05));
                sum_yu[5] = Fk_yusuan_queryeable.Sum(t => (t.tsl06));
                sum_yu[6] = Fk_yusuan_queryeable.Sum(t => (t.tsl07));
                sum_yu[7] = Fk_yusuan_queryeable.Sum(t => (t.tsl08));
                sum_yu[8] = Fk_yusuan_queryeable.Sum(t => (t.tsl09));
                sum_yu[9] = Fk_yusuan_queryeable.Sum(t => (t.tsl10));
                sum_yu[10] = Fk_yusuan_queryeable.Sum(t => (t.tsl11));
                sum_yu[11] = Fk_yusuan_queryeable.Sum(t => (t.tsl12));


                int q_year = DateTime.Now.Year;
                decimal?[] sum_sj = new decimal?[12];
                IQueryable<Fk_sjfs> sjfs = Fk_sjfsRepository.
              Instance.FindAsIQueryable(t => t.budat.Value.Year == DateTime.Now.Year);

                sum_sj[0] = sjfs.Where(w => w.budat.Value.Month == 1 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
                sum_sj[1] = sjfs.Where(w => w.budat.Value.Month == 2 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
                sum_sj[2] = sjfs.Where(w => w.budat.Value.Month == 3 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
                sum_sj[3] = sjfs.Where(w => w.budat.Value.Month == 4 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
                sum_sj[4] = sjfs.Where(w => w.budat.Value.Month == 5 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
                sum_sj[5] = sjfs.Where(w => w.budat.Value.Month == 6 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
                sum_sj[6] = sjfs.Where(w => w.budat.Value.Month == 7 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
                sum_sj[7] = sjfs.Where(w => w.budat.Value.Month == 8 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
                sum_sj[8] = sjfs.Where(w => w.budat.Value.Month == 9 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
                sum_sj[9] = sjfs.Where(w => w.budat.Value.Month == 10 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
                sum_sj[10] = sjfs.Where(w => w.budat.Value.Month == 11 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
                sum_sj[11] = sjfs.Where(w => w.budat.Value.Month == 12 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));

                rtn_hst.Add("sum_sj", sum_sj);//预算图表数据
                rtn_hst.Add("sum_yuan", sum_yu);//预算图表数据
            }

            //获取成本中心和科目的给选择
            //sql = "select distinct rcntr_no value ,rcntr label from Fk_cntr where rcntr_no in(select rcntr from fk_yusuan)";
            //dynamic[] array_cntr = DBServerProvider.SqlDapper.QueryList<object>(sql, null).ToArray();

            IQueryable<Fk_yusuan> iq_yuan = DBServerProvider.DbContext.Set<Fk_yusuan>();
            string[] arry_rcntr = iq_yuan.Select(s => s.rcntr).Distinct().ToArray();
            /* foreach(string item in arry_rcntr)
             {
                 Console.WriteLine(item);
             }*/
            IQueryable<Fk_cntr> Fk_cntr_queryeable = Fk_cntrRepository.
                Instance.FindAsIQueryable(t => arry_rcntr.Contains(t.rcntr_no));//linq的in 的使用
            dynamic[] array_cntr = Fk_cntr_queryeable.Select(s => new { value = s.rcntr_no, label = s.rcntr }).Distinct().ToArray();//linq的select的别名使用,多字段select动态接受
            rtn_hst.Add("array_cntr", array_cntr);


            sql = "select distinct fei_typename label,racct value from fei_typelist where racct in(select racct from fk_yusuan )";
            dynamic[] array_typelist = DBServerProvider.SqlDapper.QueryList<object>(sql, null).ToArray();
            rtn_hst.Add("array_typelist", array_typelist);


            dynamic[] array_year = iq_yuan.Select(s => new { value = s.gjahr, label = s.gjahr.ToString() + "年度" }).Distinct().ToArray();
            rtn_hst.Add("array_year", array_year);

            //获取当前用户的角色，用于判断前端是否显示图表信息
            int uid = UserContext.Current.GetUserInfo(UserContext.Current.UserId).User_Id;
            List<int> curr_role_list = DBServerProvider.DbContext.Set<Sys_UserRole>().Where(w => w.UserId == uid).Select(s => s.RoleId).ToList();
            if (uid==1)  curr_role_list.Add(1);//如果是超级管理
            rtn_hst.Add("curr_role_list", curr_role_list);

            return rtn_hst;
        }

        /// <summary>
        /// HOME页查询
        /// </summary>
        /// <returns></returns>
        [HttpPost, Route("select_yusuan_home"), AllowAnonymous]
        public object select_yusuan_home(string pdata)//多参数查询,pdata是对象{}，直接访问属性
        {
            dynamic queryjson = JsonConvert.DeserializeObject(pdata);
            Hashtable rtn_hst = new Hashtable();
            //string sql = "";
            IQueryable<Fk_yusuan> fys = null;
            IQueryable<Fk_sjfs> sjfs = null;
            decimal?[] sum_yu = new decimal?[12];
            decimal?[] sum_sj = new decimal?[12];
            string q_racct = queryjson.racct;
            string q_cntr = queryjson.cntr;
            int? q_year = Convert.ToInt32(queryjson.year);
            //Console.WriteLine(q_year);
            if ((q_racct == "all") && (q_cntr != "all"))
            {
                fys = Fk_yusuanRepository.Instance.FindAsIQueryable(t => t.rcntr == q_cntr && t.gjahr == q_year);
                sjfs = Fk_sjfsRepository.Instance.FindAsIQueryable(t => t.rcntr == q_cntr && t.budat.Value.Year == q_year);
            }
            if ((q_racct != "all") && (q_cntr == "all"))
            {
                fys = Fk_yusuanRepository.Instance.FindAsIQueryable(t => t.racct == q_racct && t.gjahr == q_year);
                sjfs = Fk_sjfsRepository.Instance.FindAsIQueryable(t => t.racct == q_racct && t.budat.Value.Year == q_year);
            }

            if ((q_racct == "all") && (q_cntr == "all"))
            {
                fys = Fk_yusuanRepository.Instance.FindAsIQueryable(t => (1 == 1) && t.gjahr == q_year);
                sjfs = Fk_sjfsRepository.Instance.FindAsIQueryable(t => (1 == 1) && t.budat.Value.Year == q_year);
            }

            if ((q_racct != "all") && (q_cntr != "all"))
            {
                fys = Fk_yusuanRepository.Instance.FindAsIQueryable(t => (t.racct == q_racct && t.rcntr == q_cntr && t.gjahr == q_year));
                sjfs = Fk_sjfsRepository.Instance.FindAsIQueryable(t => (t.racct == q_racct && t.rcntr == q_cntr && t.budat.Value.Year == q_year));
            }

            sum_yu[0] = fys.Sum(t => (t.tsl01));
            sum_yu[1] = fys.Sum(t => (t.tsl02));
            sum_yu[2] = fys.Sum(t => (t.tsl03));
            sum_yu[3] = fys.Sum(t => (t.tsl04));
            sum_yu[4] = fys.Sum(t => (t.tsl05));
            sum_yu[5] = fys.Sum(t => (t.tsl06));
            sum_yu[6] = fys.Sum(t => (t.tsl07));
            sum_yu[7] = fys.Sum(t => (t.tsl08));
            sum_yu[8] = fys.Sum(t => (t.tsl09));
            sum_yu[9] = fys.Sum(t => (t.tsl10));
            sum_yu[10] = fys.Sum(t => (t.tsl11));
            sum_yu[11] = fys.Sum(t => (t.tsl12));

            sum_sj[0] = sjfs.Where(w => w.budat.Value.Month == 1 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
            sum_sj[1] = sjfs.Where(w => w.budat.Value.Month == 2 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
            sum_sj[2] = sjfs.Where(w => w.budat.Value.Month == 3 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
            sum_sj[3] = sjfs.Where(w => w.budat.Value.Month == 4 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
            sum_sj[4] = sjfs.Where(w => w.budat.Value.Month == 5 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
            sum_sj[5] = sjfs.Where(w => w.budat.Value.Month == 6 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
            sum_sj[6] = sjfs.Where(w => w.budat.Value.Month == 7 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
            sum_sj[7] = sjfs.Where(w => w.budat.Value.Month == 8 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
            sum_sj[8] = sjfs.Where(w => w.budat.Value.Month == 9 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
            sum_sj[9] = sjfs.Where(w => w.budat.Value.Month == 10 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
            sum_sj[10] = sjfs.Where(w => w.budat.Value.Month == 11 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));
            sum_sj[11] = sjfs.Where(w => w.budat.Value.Month == 12 && w.budat.Value.Year == q_year).Sum(t => (t.hsl));

            rtn_hst.Add("sum_yuan", sum_yu);
            rtn_hst.Add("sum_sj", sum_sj);
            return rtn_hst;
        }


        /// <summary>
        /// 预算执行分析
        /// </summary>
        /// <returns></returns>
        [HttpPost, Route("ys_analyse_init"), AllowAnonymous]
        public object ys_analyse_init()//多参数查询,pdata是对象{}，直接访问属性
        {
           
            Hashtable rtn_hst = new Hashtable();
            //给select下拉初始化begin===========================================================================================
            IQueryable<Fk_yusuan> iq_yuan = DBServerProvider.DbContext.Set<Fk_yusuan>();
            string[] arry_rcntr = iq_yuan.Select(s => s.rcntr).Distinct().ToArray();
            IQueryable<Fk_cntr> Fk_cntr_queryeable = Fk_cntrRepository.
                Instance.FindAsIQueryable(t => arry_rcntr.Contains(t.rcntr_no));//linq的in 的使用
            dynamic[] array_cntr = Fk_cntr_queryeable.Select(s => new { value = s.rcntr_no, label = s.rcntr }).Distinct().ToArray();//linq的select的别名使用,多字段select动态接受
            rtn_hst.Add("array_cntr", array_cntr);


            string sql = "select distinct fei_typename label,racct value from fei_typelist where racct in(select racct from fk_yusuan )";
            dynamic[] array_typelist = DBServerProvider.SqlDapper.QueryList<object>(sql, null).ToArray();
            rtn_hst.Add("array_typelist", array_typelist);


            dynamic[] array_year = iq_yuan.Select(s => new { value = s.gjahr, label = s.gjahr.ToString() + "年度" }).Distinct().ToArray();
            rtn_hst.Add("array_year", array_year);
            //给select下拉初始化end=====================================================================================
            return rtn_hst;
        }

        /// <summary>
        /// 预算明细
        /// </summary>
        /// <returns></returns>
        [HttpPost, Route("ys_analyse_detail"), AllowAnonymous]
        public object ys_analyse_detail(string options)//多参数查询,pdata是对象{}，直接访问属性
        {
            Hashtable rtn_hst = new Hashtable();
            dynamic queryjson = JsonConvert.DeserializeObject(options);
            string cn_rcntr = queryjson.rcntr;
            string cn_racct = queryjson.racct;
            string cn_rcntr_name = queryjson.rcntr_name;
            string cn_racct_name = queryjson.racct_name;
            string year = queryjson.year;
            string month = queryjson.month;

            
            IQueryable<Fk_sjfs> sj = Fk_sjfsRepository.//实际发生额from SAP
          Instance.FindAsIQueryable(t => ((t.racct == cn_racct) && (t.rcntr == cn_rcntr) && (t.budat.Value.Year.ToString() == year) && (t.budat.Value.Month.ToString() == month)));


            IQueryable<Fk_yusuan> ys = Fk_yusuanRepository.//预算
            Instance.FindAsIQueryable(t => ((t.racct == cn_racct) && (t.rcntr == cn_rcntr) && (t.gjahr.Value.ToString() == year)));

            rtn_hst.Add("ys", ys);
            rtn_hst.Add("sj", sj);
            rtn_hst.Add("rcntr_name", cn_rcntr_name);
            rtn_hst.Add("racct_name", cn_racct_name);
            //Console.WriteLine(queryjson);
            return rtn_hst;
        }

            /// <summary>
            /// 预算执行分析
            /// </summary>
            /// <returns></returns>
            [HttpPost, Route("ys_analyse"), AllowAnonymous]
        public object ys_analyse(string options)//多参数查询,pdata是对象{}，直接访问属性
        {
           
            dynamic queryjson = JsonConvert.DeserializeObject(options);

            Hashtable rtn_hst = new Hashtable();
            dynamic wheres = queryjson.wheres.form;

            int currentpage = queryjson.currentpage;//当前页
            int pagesize = queryjson.pagesize;//每页条数

            IQueryable<Fei_typelist> _km_query = Fei_typelistRepository.//科目
              Instance.FindAsIQueryable(t => (t.company_oa_id == 9000));//9000和8000的科目是一样的

            IQueryable<Fk_cntr> _cntr_query = Fk_cntrRepository.//成本中心
              Instance.FindAsIQueryable(t => (true));

            int cu_month = 0;
            int cu_year = 0;
            string cu_rcntr = "";//成本中心
            string cu_racct = "";//科目
            prov.NumberFormatInfo provider = new prov.NumberFormatInfo();

            if (Convert.ToString(wheres.month).Length > 0)//判断查询条件不为空
            {
                cu_month = Convert.ToInt32(wheres.month);
            }
            else
            {
                cu_month = DateTime.Now.Month;
            }
            if (Convert.ToString(wheres.year).Length > 0)//判断查询条件不为空
            {

                cu_year = Convert.ToInt32(wheres.year);
            }
            else
            {

                cu_year = DateTime.Now.Year;

            }
            if (((JObject)wheres).Count != 0)//判断查询条件不为空
            {
                cu_racct = wheres.racct;
                cu_rcntr = wheres.cntr;

            }
            //dynamic sum_sjfs = null; 
            IQueryable<Fk_sjfs> _query = Fk_sjfsRepository.//实际发生额from SAP
           Instance.FindAsIQueryable(t => (true));




            IQueryable<Fk_yusuan> _y_query = Fk_yusuanRepository.//累计预算
            Instance.FindAsIQueryable(t => (true));
            IQueryable<Yus_Analyse> sum_sjfs = null;//全年累计实际发生额from SAP
            IQueryable<Yus_Analyse> sum_byue_sjfs = null;//本月实际发生额from SAP
            IQueryable<Yus_Analyse> sum_ym_yus = null;//年/月度预算合计

            if ((cu_racct == "all") && (cu_rcntr == "all"))
            {
                sum_sjfs = from q in _query
                           where q.budat.Value.Year == cu_year && q.budat.Value.Month <= cu_month
                           orderby q.racct descending
                           group q by new { q.racct, q.rcntr } into g// group 你想要的字段 by new{分类字段}
                           select new Yus_Analyse { rcntr = g.Key.rcntr, racct = g.Key.racct, s_sj_year = g.Sum(s => s.hsl) };
                sum_byue_sjfs = from q in _query
                                where q.budat.Value.Year == cu_year && q.budat.Value.Month == cu_month
                                orderby q.racct descending
                                group q by new { q.racct, q.rcntr } into g
                                select new Yus_Analyse { rcntr = g.Key.rcntr, racct = g.Key.racct, s_sj_yue = g.Sum(s => s.hsl) };
                if (cu_month == 1)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year
                                 group q by new { q.racct, q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     rcntr = g.Key.rcntr,
                                     racct = g.Key.racct,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0),
                                     // s_ys_year_1_10 = g.Sum(s => s.tsl01 ?? 0),
                                     // s_ys_year_11_12 = 0,
                                     s_ys_month = g.Sum(s => s.tsl01 ?? 0)
                                 };
                }
                if (cu_month == 2)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year
                                 group q by new { q.racct, q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     rcntr = g.Key.rcntr,
                                     racct = g.Key.racct,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0),
                                     //s_ys_year_1_10 = g.Sum(s => s.tsl01?? 0)  + g.Sum(s => s.tsl02?? 0) ,
                                     //s_ys_year_11_12 = 0,
                                     s_ys_month = g.Sum(s => s.tsl02 ?? 0),
                                 };
                }
                if (cu_month == 3)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year
                                 group q by new { q.racct, q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     rcntr = g.Key.rcntr,
                                     racct = g.Key.racct,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0),
                                     //s_ys_year_1_10 = g.Sum(s => s.tsl01?? 0)  + g.Sum(s => s.tsl02?? 0)  + g.Sum(s => s.tsl03?? 0) ,
                                     //s_ys_year_11_12 = 0,
                                     s_ys_month = g.Sum(s => s.tsl03 ?? 0)
                                 };
                }
                if (cu_month == 4)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year
                                 group q by new { q.racct, q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     rcntr = g.Key.rcntr,
                                     racct = g.Key.racct,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0),
                                     //s_ys_year_1_10 = g.Sum(s => s.tsl01?? 0)  + g.Sum(s => s.tsl02?? 0)  + g.Sum(s => s.tsl03?? 0)  + g.Sum(s => s.tsl04?? 0) ,
                                     //s_ys_year_11_12 = 0,
                                     s_ys_month = g.Sum(s => s.tsl04 ?? 0)
                                 };
                }
                if (cu_month == 5)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year
                                 group q by new { q.racct, q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     rcntr = g.Key.rcntr,
                                     racct = g.Key.racct,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0),
                                     //s_ys_year_1_10 = g.Sum(s => s.tsl01?? 0)  + g.Sum(s => s.tsl02?? 0)  + g.Sum(s => s.tsl03?? 0)  + g.Sum(s => s.tsl04?? 0)  + g.Sum(s => s.tsl05?? 0) ,
                                     //s_ys_year_11_12 = 0,
                                     s_ys_month = g.Sum(s => s.tsl05 ?? 0)
                                 };
                }
                if (cu_month == 6)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year
                                 group q by new { q.racct, q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     rcntr = g.Key.rcntr,
                                     racct = g.Key.racct,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0) + g.Sum(s => s.tsl06 ?? 0),
                                     //s_ys_year_1_10 = g.Sum(s => s.tsl01?? 0)  + g.Sum(s => s.tsl02?? 0)  + g.Sum(s => s.tsl03?? 0)  + g.Sum(s => s.tsl04?? 0)  + g.Sum(s => s.tsl05?? 0)  + g.Sum(s => s.tsl06?? 0) ,
                                     //s_ys_year_11_12 = 0,
                                     s_ys_month = g.Sum(s => s.tsl06 ?? 0)
                                 };
                }
                if (cu_month == 7)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year
                                 group q by new { q.racct, q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     rcntr = g.Key.rcntr,
                                     racct = g.Key.racct,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0) + g.Sum(s => s.tsl06 ?? 0) + g.Sum(s => s.tsl07 ?? 0),
                                     //s_ys_year_1_10 = g.Sum(s => s.tsl01?? 0)  + g.Sum(s => s.tsl02?? 0)  + g.Sum(s => s.tsl03?? 0)  + g.Sum(s => s.tsl04?? 0)  + g.Sum(s => s.tsl05?? 0)  + g.Sum(s => s.tsl06?? 0)  + g.Sum(s => s.tsl07?? 0) ,
                                     //s_ys_year_11_12 = 0,
                                     s_ys_month = g.Sum(s => s.tsl07 ?? 0)
                                 };
                }
                if (cu_month == 8)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year
                                 group q by new { q.racct, q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     rcntr = g.Key.rcntr,
                                     racct = g.Key.racct,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0) + g.Sum(s => s.tsl06 ?? 0) + g.Sum(s => s.tsl07 ?? 0) + g.Sum(s => s.tsl08 ?? 0),
                                     //s_ys_year_1_10 = g.Sum(s => s.tsl01?? 0)  + g.Sum(s => s.tsl02?? 0)  + g.Sum(s => s.tsl03?? 0)  + g.Sum(s => s.tsl04?? 0)  + g.Sum(s => s.tsl05?? 0)  + g.Sum(s => s.tsl06?? 0)  + g.Sum(s => s.tsl07?? 0)  + g.Sum(s => s.tsl08?? 0) ,
                                     //s_ys_year_11_12 = 0,
                                     s_ys_month = g.Sum(s => s.tsl08 ?? 0)
                                 };
                }
                if (cu_month == 9)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year
                                 group q by new { q.racct, q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     rcntr = g.Key.rcntr,
                                     racct = g.Key.racct,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0) + g.Sum(s => s.tsl06 ?? 0) + g.Sum(s => s.tsl07 ?? 0) + g.Sum(s => s.tsl08 ?? 0) + g.Sum(s => s.tsl09 ?? 0),
                                     //s_ys_year_1_10 = g.Sum(s => s.tsl01?? 0)  + g.Sum(s => s.tsl02?? 0)  + g.Sum(s => s.tsl03?? 0)  + g.Sum(s => s.tsl04?? 0)  + g.Sum(s => s.tsl05?? 0)  + g.Sum(s => s.tsl06?? 0)  + g.Sum(s => s.tsl07?? 0)  + g.Sum(s => s.tsl08?? 0)  + g.Sum(s => s.tsl09?? 0) ,
                                     //s_ys_year_11_12 = 0,
                                     s_ys_month = g.Sum(s => s.tsl09 ?? 0)
                                 };
                }
                if (cu_month == 10)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year
                                 group q by new { q.racct, q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     rcntr = g.Key.rcntr,
                                     racct = g.Key.racct,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0) + g.Sum(s => s.tsl06 ?? 0) + g.Sum(s => s.tsl07 ?? 0) + g.Sum(s => s.tsl08 ?? 0) + g.Sum(s => s.tsl09 ?? 0) + g.Sum(s => s.tsl10 ?? 0),
                                     //s_ys_year_1_10 = g.Sum(s => s.tsl01?? 0)  + g.Sum(s => s.tsl02?? 0)  + g.Sum(s => s.tsl03?? 0)  + g.Sum(s => s.tsl04?? 0)  + g.Sum(s => s.tsl05?? 0)  + g.Sum(s => s.tsl06?? 0)  + g.Sum(s => s.tsl07?? 0)  + g.Sum(s => s.tsl08?? 0)  + g.Sum(s => s.tsl09?? 0)  + g.Sum(s => s.tsl10?? 0) ,
                                     //s_ys_year_11_12 = 0,
                                     s_ys_month = g.Sum(s => s.tsl10 ?? 0)
                                 };
                }
                if (cu_month == 11)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year
                                 group q by new { q.racct, q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     rcntr = g.Key.rcntr,
                                     racct = g.Key.racct,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0) + g.Sum(s => s.tsl06 ?? 0) + g.Sum(s => s.tsl07 ?? 0) + g.Sum(s => s.tsl08 ?? 0) + g.Sum(s => s.tsl09 ?? 0) + g.Sum(s => s.tsl10 ?? 0) + g.Sum(s => s.tsl11 ?? 0),
                                     //s_ys_year_1_10 = g.Sum(s => s.tsl01?? 0)  + g.Sum(s => s.tsl02?? 0)  + g.Sum(s => s.tsl03?? 0)  + g.Sum(s => s.tsl04?? 0)  + g.Sum(s => s.tsl05?? 0)  + g.Sum(s => s.tsl06?? 0)  + g.Sum(s => s.tsl07?? 0)  + g.Sum(s => s.tsl08?? 0)  + g.Sum(s => s.tsl09?? 0)  + g.Sum(s => s.tsl10?? 0) ,
                                     //s_ys_year_11_12 = g.Sum(s => s.tsl11?? 0) ,
                                     s_ys_month = g.Sum(s => s.tsl11 ?? 0)
                                 };
                }
                if (cu_month == 12)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year
                                 group q by new { q.racct, q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     rcntr = g.Key.rcntr,
                                     racct = g.Key.racct,
                                     //s_ys_year_1_10 = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0) + g.Sum(s => s.tsl06 ?? 0) + g.Sum(s => s.tsl07 ?? 0) + g.Sum(s => s.tsl08 ?? 0) + g.Sum(s => s.tsl09 ?? 0) + g.Sum(s => s.tsl10 ?? 0),
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0) + g.Sum(s => s.tsl06 ?? 0) + g.Sum(s => s.tsl07 ?? 0) + g.Sum(s => s.tsl08 ?? 0) + g.Sum(s => s.tsl09 ?? 0) + g.Sum(s => s.tsl10 ?? 0) + g.Sum(s => s.tsl11 ?? 0) + g.Sum(s => s.tsl12 ?? 0),
                                     //s_ys_year_11_12 = g.Sum(s => s.tsl11 ?? 0) + g.Sum(s => s.tsl12 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl12 ?? 0)
                                 };
                }



            }
            if ((cu_racct != "all") && (cu_rcntr == "all"))
            {
                sum_sjfs = from q in _query
                           where q.budat.Value.Year == cu_year && q.budat.Value.Month <= cu_month && q.racct == cu_racct
                           orderby q.racct descending
                           group q by new { q.rcntr } into g
                           //select new { g.Key.rcntr, racct = cu_racct, s_sj_year = g.Sum(s => s.hsl) };
                           select new Yus_Analyse { rcntr = g.Key.rcntr, racct = cu_racct, s_sj_year = g.Sum(s => s.hsl) };
                sum_byue_sjfs = from q in _query
                                where q.budat.Value.Year == cu_year && q.budat.Value.Month == cu_month && q.racct == cu_racct
                                orderby q.racct descending
                                group q by new { q.rcntr } into g
                                select new Yus_Analyse { rcntr = g.Key.rcntr, racct = cu_racct, s_sj_yue = g.Sum(s => s.hsl) };
                if (cu_month == 1)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.racct == cu_racct
                                 group q by new { q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     rcntr = g.Key.rcntr,
                                     racct = cu_racct,
                                     s_ys_year = g.Sum(s => s.tsl01??0),
                                     s_ys_month = g.Sum(s => s.tsl01 ?? 0)
                                 };
                }
                if (cu_month == 2)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.racct == cu_racct
                                 group q by new { q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     rcntr = g.Key.rcntr,
                                     racct = cu_racct,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl02 ?? 0)
                                 };
                }
                if (cu_month == 3)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.racct == cu_racct
                                 group q by new { q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     rcntr = g.Key.rcntr,
                                     racct = cu_racct,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl03 ?? 0)
                                 };
                }
                if (cu_month == 4)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.racct == cu_racct
                                 group q by new { q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     rcntr = g.Key.rcntr,
                                     racct = cu_racct,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl04 ?? 0)
                                 };
                }
                if (cu_month == 5)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.racct == cu_racct
                                 group q by new { q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     rcntr = g.Key.rcntr,
                                     racct = cu_racct,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl05 ?? 0)
                                 };
                }
                if (cu_month == 6)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.racct == cu_racct
                                 group q by new { q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     rcntr = g.Key.rcntr,
                                     racct = cu_racct,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0) + g.Sum(s => s.tsl06 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl06 ?? 0)
                                 };
                }
                if (cu_month == 7)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.racct == cu_racct
                                 group q by new { q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     rcntr = g.Key.rcntr,
                                     racct = cu_racct,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0) + g.Sum(s => s.tsl06 ?? 0) + g.Sum(s => s.tsl07 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl07 ?? 0)
                                 };
                }
                if (cu_month == 8)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.racct == cu_racct
                                 group q by new { q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     rcntr = g.Key.rcntr,
                                     racct = cu_racct,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0) + g.Sum(s => s.tsl06 ?? 0) + g.Sum(s => s.tsl07 ?? 0) + g.Sum(s => s.tsl08 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl08 ?? 0)
                                 };
                }
                if (cu_month == 9)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.racct == cu_racct
                                 group q by new { q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     rcntr = g.Key.rcntr,
                                     racct = cu_racct,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0) + g.Sum(s => s.tsl06 ?? 0) + g.Sum(s => s.tsl07 ?? 0) + g.Sum(s => s.tsl08 ?? 0) + g.Sum(s => s.tsl09 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl09 ?? 0)
                                 };
                }
                if (cu_month == 10)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.racct == cu_racct
                                 group q by new { q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     rcntr = g.Key.rcntr,
                                     racct = cu_racct,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0) + g.Sum(s => s.tsl06 ?? 0) + g.Sum(s => s.tsl07 ?? 0) + g.Sum(s => s.tsl08 ?? 0) + g.Sum(s => s.tsl09 ?? 0) + g.Sum(s => s.tsl10 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl10 ?? 0)
                                 };
                }
                if (cu_month == 11)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.racct == cu_racct
                                 group q by new { q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     rcntr = g.Key.rcntr,
                                     racct = cu_racct,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0) + g.Sum(s => s.tsl06 ?? 0) + g.Sum(s => s.tsl07 ?? 0) + g.Sum(s => s.tsl08 ?? 0) + g.Sum(s => s.tsl09 ?? 0) + g.Sum(s => s.tsl10 ?? 0) + g.Sum(s => s.tsl11 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl11 ?? 0)
                                 };
                }
                if (cu_month == 12)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.racct == cu_racct
                                 group q by new { q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     rcntr = g.Key.rcntr,
                                     racct = cu_racct,
                                     s_ys_year = (g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0) + g.Sum(s => s.tsl06 ?? 0) + g.Sum(s => s.tsl07 ?? 0) + g.Sum(s => s.tsl08 ?? 0) + g.Sum(s => s.tsl09 ?? 0) + g.Sum(s => s.tsl10 ?? 0)) + (g.Sum(s => s.tsl11 ?? 0) + g.Sum(s => s.tsl12 ?? 0)),
                                     s_ys_month = g.Sum(s => s.tsl12 ?? 0)
                                 };
                }

            }

            if ((cu_racct == "all") && (cu_rcntr != "all"))
            {
                sum_sjfs = from q in _query
                           where q.budat.Value.Year == cu_year && q.budat.Value.Month <= cu_month && q.rcntr == cu_rcntr
                           orderby q.racct descending
                           group q by new { q.racct } into g
                           //select new { g.Key.racct, rcntr = cu_rcntr, s_sj_year = g.Sum(s => s.hsl) };
                           select new Yus_Analyse { racct = g.Key.racct, rcntr = cu_rcntr, s_sj_year = g.Sum(s => s.hsl) };
                sum_byue_sjfs = from q in _query
                                where q.budat.Value.Year == cu_year && q.budat.Value.Month == cu_month && q.rcntr == cu_rcntr
                                orderby q.racct descending
                                group q by new { q.racct } into g
                                select new Yus_Analyse { racct = g.Key.racct, rcntr = cu_rcntr, s_sj_yue = g.Sum(s => s.hsl) };
                if (cu_month == 1)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.rcntr == cu_rcntr
                                 group q by new { q.racct } into g
                                 select new Yus_Analyse
                                 {
                                     racct = g.Key.racct,
                                     rcntr = cu_rcntr,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl01 ?? 0)
                                 };
                }
                if (cu_month == 2)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.rcntr == cu_rcntr
                                 group q by new { q.racct } into g
                                 select new Yus_Analyse
                                 {
                                     racct = g.Key.racct,
                                     rcntr = cu_rcntr,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl02 ?? 0)
                                 };
                }
                if (cu_month == 3)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.rcntr == cu_rcntr
                                 group q by new { q.racct } into g
                                 select new Yus_Analyse
                                 {
                                     racct = g.Key.racct,
                                     rcntr = cu_rcntr,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl03 ?? 0)
                                 };
                }
                if (cu_month == 4)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.rcntr == cu_rcntr
                                 group q by new { q.racct } into g
                                 select new Yus_Analyse
                                 {
                                     racct = g.Key.racct,
                                     rcntr = cu_rcntr,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl04 ?? 0)
                                 };
                }
                if (cu_month == 5)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.rcntr == cu_rcntr
                                 group q by new { q.racct } into g
                                 select new Yus_Analyse
                                 {
                                     racct = g.Key.racct,
                                     rcntr = cu_rcntr,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl05 ?? 0)
                                 };
                }
                if (cu_month == 6)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.rcntr == cu_rcntr
                                 group q by new { q.racct } into g
                                 select new Yus_Analyse
                                 {
                                     racct = g.Key.racct,
                                     rcntr = cu_rcntr,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0) + g.Sum(s => s.tsl06 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl06 ?? 0)
                                 };
                }
                if (cu_month == 7)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.rcntr == cu_rcntr
                                 group q by new { q.racct } into g
                                 select new Yus_Analyse
                                 {
                                     racct = g.Key.racct,
                                     rcntr = cu_rcntr,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0) + g.Sum(s => s.tsl06 ?? 0) + g.Sum(s => s.tsl07 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl07 ?? 0)
                                 };
                }
                if (cu_month == 8)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.rcntr == cu_rcntr
                                 group q by new { q.racct } into g
                                 select new Yus_Analyse
                                 {
                                     racct = g.Key.racct,
                                     rcntr = cu_rcntr,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0) + g.Sum(s => s.tsl06 ?? 0) + g.Sum(s => s.tsl07 ?? 0) + g.Sum(s => s.tsl08 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl08 ?? 0)
                                 };
                }
                if (cu_month == 9)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.rcntr == cu_rcntr
                                 group q by new { q.racct } into g
                                 select new Yus_Analyse
                                 {
                                     racct = g.Key.racct,
                                     rcntr = cu_rcntr,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0) + g.Sum(s => s.tsl06 ?? 0) + g.Sum(s => s.tsl07 ?? 0) + g.Sum(s => s.tsl08 ?? 0) + g.Sum(s => s.tsl09 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl09 ?? 0)
                                 };
                }
                if (cu_month == 10)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.rcntr == cu_rcntr
                                 group q by new { q.racct } into g
                                 select new Yus_Analyse
                                 {
                                     racct = g.Key.racct,
                                     rcntr = cu_rcntr,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0) + g.Sum(s => s.tsl06 ?? 0) + g.Sum(s => s.tsl07 ?? 0) + g.Sum(s => s.tsl08 ?? 0) + g.Sum(s => s.tsl09 ?? 0) + g.Sum(s => s.tsl10 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl10 ?? 0)
                                 };
                }
                if (cu_month == 11)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.rcntr == cu_rcntr
                                 group q by new { q.racct } into g
                                 select new Yus_Analyse
                                 {
                                     racct = g.Key.racct,
                                     rcntr = cu_rcntr,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0) + g.Sum(s => s.tsl06 ?? 0) + g.Sum(s => s.tsl07 ?? 0) + g.Sum(s => s.tsl08 ?? 0) + g.Sum(s => s.tsl09 ?? 0) + g.Sum(s => s.tsl10 ?? 0) + g.Sum(s => s.tsl11 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl11 ?? 0)
                                 };
                }
                if (cu_month == 12)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.rcntr == cu_rcntr
                                 group q by new { q.racct } into g
                                 select new Yus_Analyse
                                 {
                                     racct = g.Key.racct,
                                     rcntr = cu_rcntr,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0) + g.Sum(s => s.tsl06 ?? 0) + g.Sum(s => s.tsl07 ?? 0) + g.Sum(s => s.tsl08 ?? 0) + g.Sum(s => s.tsl09 ?? 0) + g.Sum(s => s.tsl10 ?? 0) + g.Sum(s => s.tsl11 ?? 0) + g.Sum(s => s.tsl12 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl12 ?? 0)
                                 };
                }

            }

            if ((cu_racct != "all") && (cu_rcntr != "all"))
            {
                sum_sjfs = from q in _query
                           where q.budat.Value.Year == cu_year && q.budat.Value.Month <= cu_month && q.rcntr == cu_rcntr && q.racct == cu_racct
                           orderby q.racct descending
                           group q by new { q.racct, q.rcntr } into g
                           //           select new { racct = cu_racct, rcntr = cu_rcntr, s_sj_year = g.Sum(s => s.hsl) };
                           select new Yus_Analyse { racct = cu_racct, rcntr = cu_rcntr, s_sj_year = g.Sum(s => s.hsl) };
                sum_byue_sjfs = from q in _query
                                where q.budat.Value.Year == cu_year && q.budat.Value.Month == cu_month && q.rcntr == cu_rcntr && q.racct == cu_racct
                                orderby q.racct descending
                                group q by new { q.racct, q.rcntr } into g
                                select new Yus_Analyse { racct = cu_racct, rcntr = cu_rcntr, s_sj_yue = g.Sum(s => s.hsl) };
                if (cu_month == 1)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.rcntr == cu_rcntr && q.racct == cu_racct
                                 group q by new { q.racct, q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     racct = cu_racct,
                                     rcntr = cu_rcntr,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl01 ?? 0)
                                 };
                }
                if (cu_month == 2)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.rcntr == cu_rcntr && q.racct == cu_racct
                                 group q by new { q.racct, q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     racct = cu_racct,
                                     rcntr = cu_rcntr,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl02 ?? 0)
                                 };
                }
                if (cu_month == 3)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.rcntr == cu_rcntr && q.racct == cu_racct
                                 group q by new { q.racct, q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     racct = cu_racct,
                                     rcntr = cu_rcntr,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl03 ?? 0)
                                 };
                }
                if (cu_month == 4)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.rcntr == cu_rcntr && q.racct == cu_racct
                                 group q by new { q.racct, q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     racct = cu_racct,
                                     rcntr = cu_rcntr,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl04 ?? 0)
                                 };
                }
                if (cu_month == 5)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.rcntr == cu_rcntr && q.racct == cu_racct
                                 group q by new { q.racct, q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     racct = cu_racct,
                                     rcntr = cu_rcntr,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl05 ?? 0)
                                 };
                }
                if (cu_month == 6)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.rcntr == cu_rcntr && q.racct == cu_racct
                                 group q by new { q.racct, q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     racct = cu_racct,
                                     rcntr = cu_rcntr,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0) + g.Sum(s => s.tsl06 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl06 ?? 0)
                                 };
                }
                if (cu_month == 7)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.rcntr == cu_rcntr && q.racct == cu_racct
                                 group q by new { q.racct, q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     racct = cu_racct,
                                     rcntr = cu_rcntr,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0) + g.Sum(s => s.tsl06 ?? 0) + g.Sum(s => s.tsl07 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl07 ?? 0)
                                 };
                }
                if (cu_month == 8)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.rcntr == cu_rcntr && q.racct == cu_racct
                                 group q by new { q.racct, q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     racct = cu_racct,
                                     rcntr = cu_rcntr,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0) + g.Sum(s => s.tsl06 ?? 0) + g.Sum(s => s.tsl07 ?? 0) + g.Sum(s => s.tsl08 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl08 ?? 0)
                                 };
                }
                if (cu_month == 9)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.rcntr == cu_rcntr && q.racct == cu_racct
                                 group q by new { q.racct, q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     racct = cu_racct,
                                     rcntr = cu_rcntr,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0) + g.Sum(s => s.tsl06 ?? 0) + g.Sum(s => s.tsl07 ?? 0) + g.Sum(s => s.tsl08 ?? 0) + g.Sum(s => s.tsl09 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl09 ?? 0)
                                 };
                }
                if (cu_month == 10)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.rcntr == cu_rcntr && q.racct == cu_racct
                                 group q by new { q.racct, q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     racct = cu_racct,
                                     rcntr = cu_rcntr,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0) + g.Sum(s => s.tsl06 ?? 0) + g.Sum(s => s.tsl07 ?? 0) + g.Sum(s => s.tsl08 ?? 0) + g.Sum(s => s.tsl09 ?? 0) + g.Sum(s => s.tsl10 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl10 ?? 0)
                                 };
                }
                if (cu_month == 11)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.rcntr == cu_rcntr && q.racct == cu_racct
                                 group q by new { q.racct, q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     racct = cu_racct,
                                     rcntr = cu_rcntr,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0) + g.Sum(s => s.tsl06 ?? 0) + g.Sum(s => s.tsl07 ?? 0) + g.Sum(s => s.tsl08 ?? 0) + g.Sum(s => s.tsl09 ?? 0) + g.Sum(s => s.tsl10 ?? 0) + g.Sum(s => s.tsl11 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl11 ?? 0)
                                 };
                }
                if (cu_month == 12)
                {
                    sum_ym_yus = from q in _y_query
                                 where q.gjahr == cu_year && q.rcntr == cu_rcntr && q.racct == cu_racct
                                 group q by new { q.racct, q.rcntr } into g
                                 select new Yus_Analyse
                                 {
                                     racct = cu_racct,
                                     rcntr = cu_rcntr,
                                     s_ys_year = g.Sum(s => s.tsl01 ?? 0) + g.Sum(s => s.tsl02 ?? 0) + g.Sum(s => s.tsl03 ?? 0) + g.Sum(s => s.tsl04 ?? 0) + g.Sum(s => s.tsl05 ?? 0) + g.Sum(s => s.tsl06 ?? 0) + g.Sum(s => s.tsl07 ?? 0) + g.Sum(s => s.tsl08 ?? 0) + g.Sum(s => s.tsl09 ?? 0) + g.Sum(s => s.tsl10 ?? 0) + g.Sum(s => s.tsl11 ?? 0) + g.Sum(s => s.tsl12 ?? 0),
                                     s_ys_month = g.Sum(s => s.tsl12 ?? 0)
                                 };
                }

            }



            // _km_query = _km_query.ToList();

            /* 左关联写法*/
            IQueryable<Yus_Analyse> ys_analues_query = from q1 in sum_ym_yus //年/月度预算合计

                                                       join q2 in _km_query on q1.racct equals q2.racct into q3
                                                       from q22 in q3.DefaultIfEmpty()//科目主数据

                                                       join q4 in _cntr_query on q1.rcntr equals q4.rcntr_no into q5
                                                       from q44 in q5.DefaultIfEmpty() //成本中心主数据

                                                       join sj_m1 in sum_byue_sjfs on new { q1.rcntr, q1.racct } equals new { sj_m1.rcntr, sj_m1.racct } into into_sj_m1
                                                       from sj_m11 in into_sj_m1.DefaultIfEmpty()//实际发生月度

                                                       join sj_year1 in sum_sjfs on new { q1.rcntr, q1.racct } equals new { sj_year1.rcntr, sj_year1.racct } into into_sj_year1
                                                       from sj_year11 in into_sj_year1.DefaultIfEmpty()//实际发生年累计


                                                           //where q1.racct == q22.racct && q1.rcntr == q44.rcntr_no

                                                       select new Yus_Analyse
                                                       {
                                                           racct = q1.racct,//科目编码

                                                           racct_name = q22.fei_typename,//科目名称
                                                           rcntr = q1.rcntr,//成本中心
                                                           rcntr_name = q44.rcntr,//成本中心名称
                                                           s_ys_month = q1.s_ys_month,//预算本月
                                                           s_ys_year = q1.s_ys_year,//年度累计预算,linq嵌套只有10层,用??0时一定要用括号，否则linq会有解析错误
                                                           yue_cy = (sj_m11.s_sj_yue - q1.s_ys_month),
                                                           yue_cy_ratio = CustomHelper.linq_func_nozero(((sj_m11.s_sj_yue??0) - (q1.s_ys_month??0)),(q1.s_ys_month??0)),//转换为百分比格式
                                                           s_sj_yue = sj_m11.s_sj_yue,//实际发生额本月
                                                           s_sj_year = sj_year11.s_sj_year,//实际年度累计
                                                           year_cy = (sj_year11.s_sj_year ?? 0) - (q1.s_ys_year ?? 0),//年度差异额
                                                           year_cy_ratio = CustomHelper.linq_func_nozero(((sj_year11.s_sj_year ?? 0) - (q1.s_ys_year ?? 0)) , (q1.s_ys_year??0)),
                                                           //year_cy = get_year_cy(null, 7),//年度差异额
                                                       };


            /*  rtn_hst.Add("rows", sum_ym_yus.Count());
              sum_ym_yus = sum_ym_yus.OrderByDescending(o => o.racct);
              sum_ym_yus = sum_ym_yus
               .Skip(pagesize * (currentpage - 1)).Take(pagesize);
              rtn_hst.Add("_query", sum_ym_yus);*/

            //加入到cache中用于导出使用
            ICacheService cacheService = AutofacContainerModule.GetService<ICacheService>();
            cacheService.AddObject(UserContext.Current.UserId.ToString(), ys_analues_query.ToList());


            rtn_hst.Add("rows", ys_analues_query.Count());
            ys_analues_query = ys_analues_query.OrderByDescending(o => o.racct);
            ys_analues_query = ys_analues_query
             .Skip(pagesize * (currentpage - 1)).Take(pagesize);
            rtn_hst.Add("_query", ys_analues_query);


            rtn_hst.Add("sum_ym_yus", sum_ym_yus);
            rtn_hst.Add("sum_sjfs", sum_sjfs);
            rtn_hst.Add("sum_byue_sjfs", sum_byue_sjfs);
            return rtn_hst;
        }

        /*public string? get_year_cy(decimal? a, decimal? b)
        {
            //return "=";
            if (a is null || b is null) return "-";
            decimal? c = a - b;
            string? rtn = "=";
            if (c is null)
            {
                return rtn;
            }
            else
            {
                rtn = c.ToString();
                return rtn;
            }
        }
        public decimal? customer_null(decimal? a)
        {
            if (a is null)
                return 0;
            else
                return a;
        }*/


        /// <summary>
        /// 导出Excel,使用文件流下载文件
        /// </summary>
        /// <returns></returns>
        [HttpPost, Route("customer_export"), AllowAnonymous]
        public ActionResult customer_export()
        {
            string path = null;
            string fileName = null;

            ICacheService cacheService = AutofacContainerModule.GetService<ICacheService>();
            List<Yus_Analyse> dataList = cacheService.Get<List<Yus_Analyse>>(UserContext.Current.UserId.ToString());//从内存中取出导出数据
    
            fileName = "temp.xlsx";//随便给
            path = CustomHelper.ExportGeneralExcel(dataList, fileName);

            return File(sysio.File.ReadAllBytes(path), sysnet.Mime.MediaTypeNames.Application.Octet, fileName);
        }

        

    }

}
